using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for PlaceCategoryDAO
/// </summary>
public class PlaceCategoryDAO:DBAccess
{
    string SQL_SELECT_ALL = "select * from PlaceCategory";
    string SQL_SELECT_BY_ID = "select * from PlaceCategory where ID=@id";
    string SQL_INSERT = "insert into PlaceCategory([Name], State,[ModifiedDate], ModifiedBy) values(@Name, @State,@ModifiedDate, @ModifiedBy)";
    string SQL_UPDATE = "update PlaceCategory set Name=@Name, State=@State,ModifiedDate=@ModifiedDate, ModifiedBy=@ModifiedBy where ID=@id";
    string SQL_DELETE = "delete from PlaceCategory where ID=@id";
    #region fields
    string message = "";
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
    }
    #endregion
    public PlaceCategoryDAO()
	{	
	}
    /// <summary>
    /// Get a place category by ID
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public PlaceCategory GetPlaceCategory(int id)
    {
        PlaceCategory cate = new PlaceCategory();
        try
        {

            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                if (dr.Read())
                {
                    cate.Id = dr.GetInt32(0);
                    cate.Name = dr.GetString(1);
                    cate.State = dr.GetBoolean(2);                 
                    cate.ModifiedDate = dr.GetDateTime(3);
                    cate.ModifiedBy = dr.GetInt32(4);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return cate;
    }

    /// <summary>
    /// Get all place categories
    /// </summary>
    /// <returns></returns>
    public IList<PlaceCategory> GetAllPlaceCategories()
    {
        IList<PlaceCategory> list = new List<PlaceCategory>();
        try
        {

            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            { 
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    PlaceCategory cate = new PlaceCategory();
                    cate.Id = dr.GetInt32(0);
                    cate.Name = dr.GetString(1);
                    cate.State = dr.GetBoolean(2);
                    cate.ModifiedDate = dr.GetDateTime(3);
                    cate.ModifiedBy = dr.GetInt32(4);
                    list.Add(cate);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Add new place information
    /// </summary>
    /// <param name="cate"></param>
    /// <returns></returns>
    public int Insert(PlaceCategory cate)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@Name", cate.Name);
            command.Parameters.AddWithValue("@State", cate.State);
            command.Parameters.AddWithValue("@ModifiedDate", cate.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", cate.ModifiedBy);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = "SELECT @@IDENTITY from PlaceCategory";
                cate.Id = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Update place information
    /// </summary>
    /// <param name="cate"></param>
    /// <returns></returns>
    public int Update(PlaceCategory cate)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@Name", cate.Name);
            command.Parameters.AddWithValue("@State", cate.State);
            command.Parameters.AddWithValue("@ModifiedDate", cate.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", cate.ModifiedBy);
            command.Parameters.AddWithValue("@id", cate.Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();                
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Delete a place information by specified ID
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public int Delete(int id)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);            
            command.Parameters.AddWithValue("@id", id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }
}
